﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
//using MySql.Data.MySqlClient;
using System.Collections;
using System.Diagnostics;
using System.IO;
//using MixScript.Appl.Config;


//using System.Drawing;
//using studio.server;
//using studio.AppLib;

namespace Extractor.LibCode
{
    public abstract class cAppDal
    {
        internal cBaseConfig cnfg;
        private string constring;
        private System.Data.Common.DbConnection dbconn;
        private System.Data.Common.DbDataAdapter dbdataadapter;
        private System.Data.Common.DbCommand dbcommand;
        private System.Data.Common.DbTransaction dbtransaction;
        private System.Data.Common.DbParameter dbparam;

        public System.Data.Common.DbTransaction Dbtransaction
        {
            get { return dbtransaction; }
            set { dbtransaction = value; }
        }
        public System.Data.Common.DbParameter Dbparam
        {
            get { return dbparam; }
            set { dbparam = value; }
        }
        public System.Data.ConnectionState StateConn
        {
            get
            {
                return Dbconn.State;
            }

        }
        public System.Data.Common.DbCommand Dbcommand
        {
            get { return dbcommand; }
            set { dbcommand = value; }
        }
        public System.Data.Common.DbDataAdapter Dbdataadapter
        {
            get { return dbdataadapter; }
            set { dbdataadapter = value; }
        }
        public System.Data.Common.DbConnection Dbconn
        {
            get { return dbconn; }
            set { dbconn = value; }
        }
        public string strConnstring
        {
            get { return constring; }
            set { constring = value; }
        }

        public cAppDal()
        {
            cnfg = new cBaseConfig();
        }

        public abstract int SqlUpdate(DataTable tbl, string tableName, out string errString);
        public abstract bool ExecuteNonQuery(string query);
        public abstract bool openConn();
        public abstract void CommitAndCloseConn();
        public abstract void ForceRollBackNClose();
        public abstract void ForceRollBack();
        public abstract DataTable ExecuteQuery(string strQuery, string strTableName);
        public abstract bool ExecuteQuery(string strQuery);
        public abstract DataTable ExecuteStoredProcedure(string spName, System.Collections.ArrayList spParam);
        public abstract DataTable ExecuteStoredProcedure(string spName);
        public abstract void genConstring();
        public abstract bool tesConn();
        public abstract bool tesConn(string sConstring);
        public abstract bool ExecuteNonQuery(string query, ArrayList sqlParam);
        //public abstract object ExecuteScalar(string query);

        public object ExecuteScalar(string query)
        {
            try
            {
                DataTable dt = new DataTable();
                dt = ExecuteQuery(query, "");
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0];
                }
                else
                {
                    return "";
                }
            }
            catch (Exception ex)
            {

                throw;
            }
        }

    }

    public class cSSDAL : cAppDal
    {

        public cSSDAL()
        {
            //  //cnfg = new cConfig();
            cnfg = new cBaseConfig();
            genConstring();
            Dbconn = new SqlConnection(strConnstring);
            Dbcommand = new SqlCommand();
            Dbdataadapter = new SqlDataAdapter();
            //Dbtransaction = SqlTransaction Dbtransaction;
            Dbparam = new SqlParameter();

            //          SqlTransaction

        }



        public override bool ExecuteNonQuery(string query)
        {
            try
            {
                //SqlTransaction Dbtransaction;
                //Dbcommand = new SqlCommand(query, Dbconn, new SqlTransaction() Dbtransaction));
                Dbcommand.CommandText = query;
                Dbcommand.Connection = Dbconn;
                Dbcommand.Transaction = Dbtransaction;
                Dbcommand.ExecuteNonQuery();
                //Dbcommand.CommandType = CommandType.Text;
                //Dbcommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                if (Dbconn.State == ConnectionState.Open)
                {
                    if (Dbtransaction.Connection != null)
                    {
                        Dbtransaction.Rollback();
                    }
                    Dbconn.Close();
                }
                throw new Exception(ex.Message, ex);
            }
        }

        public override bool openConn()
        {

            try
            {
                Dbconn = new SqlConnection(strConnstring);
                Dbconn.Open();
                Dbtransaction = Dbconn.BeginTransaction();
                return true;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public override void CommitAndCloseConn()
        {


            try
            {
                if (Dbconn.State == ConnectionState.Open)
                {
                    Dbtransaction.Commit();
                    Dbconn.Close();
                }
            }
            catch (Exception ex)
            {
                if (Dbtransaction.Connection != null)
                {
                    // sqlTrans.Rollback();
                }
                throw new Exception(ex.Message, ex);
            }
        }



        public override void ForceRollBackNClose()
        {
            try
            {
                if (Dbconn.State == ConnectionState.Open)
                {
                    if (Dbtransaction.Connection != null)
                    {
                        Dbtransaction.Rollback();
                    }
                    Dbconn.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public override void ForceRollBack()
        {
            try
            {
                if (Dbconn.State == ConnectionState.Open)
                {
                    if (Dbtransaction.Connection != null)
                    {
                        Dbtransaction.Rollback();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public override System.Data.DataTable ExecuteQuery(string strQuery, string strTableName)
        {
            DataTable dt;
            //SqlDataAdapter da = new SqlDataAdapter();
            Dbdataadapter = new SqlDataAdapter();
            try
            {
                //SqlTransaction st;
                //SqlConnection dbc;
                //Dbcommand = new SqlCommand(strQuery, dbc, st);
                Dbcommand.CommandText = strQuery;
                Dbcommand.Connection = Dbconn;
                Dbcommand.Transaction = Dbtransaction;
                Dbcommand.CommandTimeout = 1200;
                //qlCommand conn= new SqlCommand
                //Dbcommand = new SqlCommand (strQuery, conn);
                dt = new DataTable(strTableName);
                //Dbcommand.CommandType = CommandType.Text;
                //da.SelectCommand =SqlCommand() Dbcommand;
                //
                Dbdataadapter.SelectCommand = Dbcommand;
                Dbdataadapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                if (Dbconn.State == ConnectionState.Open)
                {
                    if (Dbtransaction.Connection != null)
                    {
                        Dbtransaction.Rollback();
                    }
                    Dbconn.Close();
                }
                throw new Exception(ex.Message, ex);
            }
        }

        public override System.Data.DataTable ExecuteStoredProcedure(string spName, System.Collections.ArrayList spParam)
        {
            throw new NotImplementedException();
        }

        public override System.Data.DataTable ExecuteStoredProcedure(string spName)
        {
            throw new NotImplementedException();
        }

        public override void genConstring()
        {
            try
            {
                strConnstring = "Data Source=" + cnfg.DBHost + ";Initial Catalog=" + cnfg.Dbname + ";user=" + cnfg.Userdb + ";password=" + cnfg.Passdb + ";";
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        public override bool tesConn()
        {
            try
            {
                Dbconn.Open();
                Dbconn.Close();
                return true;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        public override bool tesConn(string sConstring)
        {
            try
            {
                Dbconn = new SqlConnection(sConstring);
                Dbconn.Open();
                Dbconn.Close();
                return true;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }



        public override bool ExecuteNonQuery(string query, ArrayList sqlParam)
        {
            throw new NotImplementedException();
        }

        public override int SqlUpdate(DataTable tbl, string tableName, out string errString)
        {
            int lastErrorNumber = 0;
            errString = string.Empty;
            lastErrorNumber = 0;

            if (Dbconn == null)
            {
                lastErrorNumber = -1;
                errString = "no connection.";
                return -2;
            }
            else if (Dbconn.State != ConnectionState.Open)
            {
                lastErrorNumber = -1;
                errString = "no open connection.";
                return -2;
            }
            else if (string.IsNullOrEmpty(tableName))
            {
                lastErrorNumber = -1;
                errString = "Parameter 'tableName' cannot be empty.";
                return -3;
            }

            SqlCommand sqlCmd = (SqlCommand)Dbconn.CreateCommand();
            SqlDataAdapter sqlDA = null;
            string sqlQ = string.Format("SELECT TOP 0 * FROM {0}", tableName);
            int result = 0;

            try
            {
                sqlCmd.CommandTimeout = 5000;// timeoutCommand;
                sqlCmd.CommandText = sqlQ;
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.Prepare();
                sqlCmd.Transaction = (SqlTransaction)Dbtransaction;

                sqlDA = new SqlDataAdapter(sqlCmd);
                sqlDA.ContinueUpdateOnError = false;// continuedOnError;
                SqlCommandBuilder sqlBuild = new SqlCommandBuilder(sqlDA);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                sqlDA.FillSchema(tbl, SchemaType.Mapped);
                sqlBuild = null;
                result = sqlDA.Update(tbl);
                try
                {
                    tbl.AcceptChanges();
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(
                      string.Format("KSEISqlService:SqlServices:SqlUpdate(DataTable, String) - {0}", ex.Message));

                    lastErrorNumber = -7;
                }
            }
            catch (DBConcurrencyException ex)
            {
                //if (autoRepairDbConcurrancy)
                //{
                //    try
                //    {
                //        int numberRowsAffect = RepairConcurrencyRows(tbl, tableName, cnString);
                //        if (numberRowsAffect > 0)
                //        {
                //            try
                //            {
                //                result = sqlDA.Update(tbl);
                //            }
                //            catch (Exception exChild)
                //            {
                //                Debug.WriteLine(
                //                  string.Format("KSEISqlService:SqlServices:SqlUpdate(DataTable, String) UpdateRepairConcurrency - {0}", exChild.Message));

                //                result = -5;
                //                errString = ex.Message;
                //                lastErrorNumber = -5;
                //            }
                //        }
                //    }
                //    catch (Exception exChild)
                //    {
                //        Debug.WriteLine(
                //          string.Format("KSEISqlService:SqlServices:SqlUpdate(DataTable, String) RepairConcurrency - {0}", exChild.Message));

                //        result = -6;
                //        errString = ex.Message;
                //        lastErrorNumber = -6;
                //    }
                //}
                //else
                //{
                //    result = -4;
                //    errString = ex.Message;
                //    lastErrorNumber = -4;
                //}
            }
            catch (SqlException ex)
            {
                Debug.WriteLine(
                  string.Format("KSEISqlService:SqlServices:SqlUpdate(DataTable, String) - {0}", ex.Message));

                result = -2;
                errString = ex.Message;
                lastErrorNumber = ex.Number;
            }
            catch (Exception ex)
            {
                result = -2;
                errString = ex.Message;
                lastErrorNumber = -2;
            }
            finally
            {
                if (sqlCmd != null)
                {
                    sqlCmd.Dispose();
                    sqlCmd = null;
                }

                if (sqlDA != null)
                {
                    sqlDA.Dispose();
                    sqlDA = null;
                }
            }

            return result;
            #region OldCode
            //bool autoRepairDbConcurrancy = false;
            //errString = string.Empty;
            //int lastErrorNumber = 0;

            //if (Dbconn == null)
            //{
            //    lastErrorNumber = -1;
            //    errString = "no connection.";
            //    return -2;
            //}
            //else if (Dbconn.State != ConnectionState.Open)
            //{
            //    lastErrorNumber = -1;
            //    errString = "no open connection.";
            //    return -2;
            //}
            //else if (string.IsNullOrEmpty(tableName))
            //{
            //    lastErrorNumber = -1;
            //    errString = "Parameter 'tableName' cannot be empty.";
            //    return -3;
            //}

            //Dbcommand = Dbconn.CreateCommand();
            //SqlDataAdapter sqlDA = null;
            //string sqlQ = string.Format("SELECT TOP 0 * FROM {0} ", tableName);
            //int result = 0;

            //try
            //{
            //    Dbcommand.CommandTimeout = 5000;//timeoutCommand;
            //    Dbcommand.CommandText = sqlQ;
            //    Dbcommand.CommandType = CommandType.Text;
            //    Dbcommand.Prepare();


            //    sqlDA = new SqlDataAdapter((SqlCommand)Dbcommand);
            //    sqlDA.ContinueUpdateOnError = false;// continuedOnError;
            //    SqlCommandBuilder sqlBuild = new SqlCommandBuilder(sqlDA);
            //    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            //    sqlDA.FillSchema(tbl, SchemaType.Mapped);
            //    sqlBuild = null;
            //    result = sqlDA.Update(tbl);
            //    try
            //    {
            //        tbl.AcceptChanges();
            //    }
            //    catch (Exception ex)
            //    {
            //        Debug.WriteLine(
            //          string.Format("KSEISqlService:SqlServices:SqlUpdate(DataTable, String) - {0}", ex.Message));

            //        lastErrorNumber = -7;
            //    }
            //}
            //catch (DBConcurrencyException ex)
            //{
            //    if (autoRepairDbConcurrancy)
            //    {
            //        try
            //        {
            //            int numberRowsAffect = RepairConcurrencyRows(tbl, tableName, strConnstring);
            //            if (numberRowsAffect > 0)
            //            {
            //                try
            //                {
            //                    result = sqlDA.Update(tbl);
            //                }
            //                catch (Exception exChild)
            //                {
            //                    Debug.WriteLine(
            //                      string.Format("KSEISqlService:SqlServices:SqlUpdate(DataTable, String) UpdateRepairConcurrency - {0}", exChild.Message));

            //                    result = -5;
            //                    errString = ex.Message;
            //                    lastErrorNumber = -5;
            //                }
            //            }
            //        }
            //        catch (Exception exChild)
            //        {
            //            Debug.WriteLine(
            //              string.Format("KSEISqlService:SqlServices:SqlUpdate(DataTable, String) RepairConcurrency - {0}", exChild.Message));

            //            result = -6;
            //            errString = ex.Message;
            //            lastErrorNumber = -6;
            //        }
            //    }
            //    else
            //    {
            //        result = -4;
            //        errString = ex.Message;
            //        lastErrorNumber = -4;
            //    }
            //}
            //catch (SqlException ex)
            //{
            //    Debug.WriteLine(
            //      string.Format("KSEISqlService:SqlServices:SqlUpdate(DataTable, String) - {0}", ex.Message));

            //    result = -2;
            //    errString = ex.Message;
            //    lastErrorNumber = ex.Number;
            //}
            //catch (Exception ex)
            //{
            //    result = -2;
            //    errString = ex.Message;
            //    lastErrorNumber = -2;
            //}
            ////finally
            ////{
            ////    if (Dbcommand != null)
            ////    {
            ////        Dbcommand.Dispose();
            ////        Dbcommand = null;
            ////    }

            ////    if (sqlDA != null)
            ////    {
            ////        sqlDA.Dispose();
            ////        sqlDA = null;
            ////    }
            ////}

            //return result; 
            #endregion
        }
        private int RepairConcurrencyRows(DataTable table, string tableName, string connectionString)
        {
            //if ((connectionString == null) || (table == null) || (table.Rows.Count < 1))
            //{
            //    return 0;
            //}

            //DataTable tableChanges = table.GetChanges();

            //if ((tableChanges == null) || (tableChanges.Rows.Count < 1))
            //{
            //    return 0;
            //}

            //int result = 0;
            //int nLoop = 0;
            //IDictionary<string, List<string>> dicQuery = new Dictionary<string, List<string>>(StringComparer.OrdinalIgnoreCase);
            //StringBuilder query = new StringBuilder();

            //DataRow row = null;
            //List<SqlParameter> lstParams = new List<SqlParameter>();
            //List<string> lst = null;
            //string tmpPrimaryName = null;
            //string paramName = null;

            //query.AppendFormat("SELECT * FROM {0} ", tableName);

            //for (nLoop = 0; nLoop < tableChanges.Rows.Count; nLoop++)
            //{
            //    row = tableChanges.Rows[nLoop];

            //    if (row.HasErrors)
            //    {
            //        row.ClearErrors();

            //        #region Read Db Version

            //        if (tableChanges.PrimaryKey.Length == 1)
            //        {
            //            tmpPrimaryName = tableChanges.PrimaryKey[0].ColumnName;
            //            if (dicQuery.ContainsKey(tmpPrimaryName))
            //            {
            //                lst = dicQuery[tmpPrimaryName];
            //                if (lst == null)
            //                {
            //                    lst = new List<string>();
            //                    dicQuery[tmpPrimaryName] = lst;
            //                }
            //                paramName = string.Concat("@PAR_", nLoop);
            //                lst.Add(paramName);
            //            }
            //            else
            //            {
            //                lst = new List<string>();
            //                paramName = string.Concat("@PAR_", nLoop);
            //                lst.Add(paramName);
            //                dicQuery.Add(tmpPrimaryName, lst);
            //            }

            //            // Get value
            //            lstParams.Add(ParameterBuilder(paramName, row, tmpPrimaryName));
            //        }
            //        else if (tableChanges.PrimaryKey.Length > 1)
            //        {
            //            // no Data
            //            for (int nLoopC = 0; nLoopC < tableChanges.PrimaryKey.Length; nLoopC++)
            //            {
            //                tmpPrimaryName = tableChanges.PrimaryKey[nLoopC].ColumnName;
            //                paramName = string.Concat("@PAR_", nLoop, "_", nLoopC);
            //                if (dicQuery.ContainsKey(tmpPrimaryName))
            //                {
            //                    lst = dicQuery[tmpPrimaryName];
            //                    if (lst == null)
            //                    {
            //                        lst = new List<string>();
            //                        dicQuery[tmpPrimaryName] = lst;
            //                    }
            //                    lst.Add(paramName);
            //                }
            //                else
            //                {
            //                    lst = new List<string>();
            //                    lst.Add(paramName);
            //                    dicQuery.Add(tmpPrimaryName, lst);
            //                }
            //            }
            //        }

            //        #endregion
            //    }
            //}

            //if (dicQuery.Count > 0)
            //{
            //    #region Rebuild Query

            //    query.Append(" WHERE ");
            //    bool useAnd = false;

            //    foreach (KeyValuePair<string, List<string>> kvp in dicQuery)
            //    {
            //        if (kvp.Value.Count < 1)
            //        {
            //            continue;
            //        }
            //        else if (kvp.Value.Count == 1)
            //        {
            //            if (useAnd)
            //            {
            //                query.Append(" AND ");
            //            }

            //            query.AppendFormat(" {0} = {1} ", kvp.Key, kvp.Value[0]);
            //            useAnd = true;
            //        }
            //        else
            //        {
            //            if (useAnd)
            //            {
            //                query.Append(" AND ");
            //            }

            //            query.AppendFormat(" {0} IN ({1}) ", kvp.Key, string.Join(",", kvp.Value.ToArray()));
            //            useAnd = true;
            //        }
            //    }

            //    #endregion

            //    SqlConnection newConnection = new SqlConnection(connectionString);
            //    lock (newConnection)
            //    {
            //        #region Execute Query

            //        SqlCommand command = newConnection.CreateCommand();
            //        SqlDataAdapter adapter = new SqlDataAdapter(command);
            //        DataTable tableDb = new DataTable();
            //        DataSet ds = new DataSet();
            //        bool open = false;

            //        try
            //        {
            //            if (newConnection.State != ConnectionState.Open)
            //            {
            //                newConnection.Open();
            //                open = true;
            //            }

            //            command.CommandText = query.ToString();
            //            command.CommandType = CommandType.Text;
            //            command.Parameters.AddRange(lstParams.ToArray());

            //            command.ExecuteScalar();

            //            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            //            adapter.FillSchema(tableDb, SchemaType.Mapped);

            //            ds.Tables.Add(tableDb);
            //            ds.EnforceConstraints = false;

            //            adapter.Fill(tableDb);
            //        }
            //        catch (Exception ex)
            //        {
            //            Debug.WriteLine(
            //              string.Format("RepairRows Prepare/Execute Query - {0}", ex.Message));

            //            if (tableDb != null)
            //            {
            //                tableDb.Clear();
            //                tableDb.Dispose();
            //            }
            //        }
            //        finally
            //        {
            //            ds.Tables.Remove(tableDb);
            //            ds.Clear();
            //            ds.Dispose();

            //            adapter.Dispose();

            //            command.Dispose();
            //            if (open)
            //            {
            //                try
            //                {
            //                    newConnection.Close();
            //                }
            //                catch (Exception ex)
            //                {
            //                    Debug.WriteLine(
            //                      string.Format("RepairRows CloseConnection - {0}", ex.Message));
            //                }
            //                try
            //                {
            //                    newConnection.Dispose();
            //                }
            //                catch (Exception ex)
            //                {
            //                    Debug.WriteLine(
            //                      string.Format("RepairRows DisposeConnection - {0}", ex.Message));
            //                }
            //            }
            //        }

            //        #endregion

            //        if (tableDb != null)
            //        {
            //            try
            //            {
            //                table.Merge(tableDb, true);

            //                result = tableDb.Rows.Count;
            //            }
            //            catch (Exception ex)
            //            {
            //                Debug.WriteLine(
            //                    string.Format("RepairRows Merger - {0}", ex.Message));
            //            }

            //            tableDb.Clear();
            //            tableDb.Dispose();
            //        }
            //    }
            //}

            return 0;// result;
        }


        public override bool ExecuteQuery(string strQuery)
        {
            //DataTable dt = new DataTable();
            return ExecuteQuery(strQuery, "").Rows.Count > 0;
        }
    }


    


}
